package database;

// <editor-fold defaultstate="collapsed" desc="HeaderFiles">
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;// </editor-fold>

/**
 * @version
 *
 * @Date Mar 24, 2011
 * @author Rajeeb Saha
 *
 */
public class ClerkDAO {

    // <editor-fold defaultstate="collapsed" desc="Private Variables">
    private Connection conn = null;
    private Statement sqlStatement;
    private static final Logger logger = Logger.getLogger(
            ClerkDAO.class.getName());// </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="Constructor">
    /**
     *
     */
    public ClerkDAO() {
        ConnectionDAO connectionDAO = new ConnectionDAO();
        conn = connectionDAO.getConn();
        sqlStatement = connectionDAO.getSqlStatement();
    }

    /**
     *
     * @param conn
     * @param sqlStatement
     */
    public ClerkDAO(Connection conn, Statement sqlStatement) {
        this.conn = conn;
        this.sqlStatement = sqlStatement;
    }// </editor-fold>

    /**
     *
     * @param password
     * @param firstName
     * @param lastName
     * @return
     */
    protected String registerClerk(String password, String firstName,
            String lastName) {
        String ckid = "";

        try {
            String sqlText = "select max(c.ckid) from digistore.clerk c";

            ResultSet results = sqlStatement.executeQuery(sqlText);

            results.next();
            ckid = results.getString(1);

            if (ckid == null) {
                ckid = "1000001";
            } else {
                ckid = "" + (Integer.parseInt(ckid.trim()) + 1);
            }

            //  System.out.print(ckid);

            sqlText =
                    "insert into digistore.clerk " +
                    "(ckid, passcode, firstName,lastName) values " + "(" +
                    "'" + ckid + "'," + "'" + password + "'," + "'" + firstName +
                    "'," + "'" + lastName + "')";

            int value = sqlStatement.executeUpdate(sqlText);
            if (value == 1) {
                conn.commit();
                return ckid;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : regesterClerk" + ex);
            //    throw ex;
        }
        return ckid;
    }

    /**
     * 
     * @param ckid
     * @return
     */
    protected boolean deleteClerk(int ckid) {
        try {
            String sqlText = "DELETE FROM digistore.clerk WHERE ckid=" + ckid;
            int value = sqlStatement.executeUpdate(sqlText);
            if (value == 1) {
                conn.commit();
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : deleteClerk" + ex);
        }
        return true;
    }

    /**
     *
     * @param ckid
     * @return
     */
    protected String getPassword(int ckid) {
        String resultString = "";
        try {
            String sqlText =
                    "select m.passcode " + "from digistore.clerk m " +
                    "where m.ckid = '" + ckid + "'";
            ResultSet results = sqlStatement.executeQuery(sqlText);
            if (!results.next()) { // Check user name
                resultString = "";
            } else {
                resultString = results.getString(1);
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return resultString;
    }

    /**
     *
     * @param ckid
     * @param password
     * @param firstName
     * @param lastName
     * @return
     */
    protected boolean updateClerk(int ckid, String password, String firstName,
            String lastName) {
        try {
            String sqlText = "UPDATE digistore.clerk SET passcode='" +
                    password + "', " + "firstName='" + firstName + "', " +
                    "lastName='" + lastName + "' WHERE ckid='" + ckid + "'";
            int value = sqlStatement.executeUpdate(sqlText);
            if (value == 1) {
                conn.commit();
                return true;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
